﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Web.UI.WebControls;
using System.ComponentModel;
using NotAClue;
using System.Data.SqlTypes;
//using Oracle.DataAccess.Client;

namespace NotAClue.Web
{
    public static class AdoExtensionMethods
    {
        private const object DUMMY_OBJECT = null;
        // embed T-SQL in select statement with TOP 1 rows
        //private const string commandWrapper = "SELECT TOP 1 * FROM ({0}) AS TEST";
        //private const string commandOracleWrapper = "SELECT * FROM ({0}) AS TEST WHERE ROWNUM < 2";
        //private const string sprocWrapper = "SET FMTONLY ON;" + " EXEC {0};" + " SET FMTONLY OFF;";

        #region SQL Database Type Converters
        /// <summary>
        /// Convert to Type from SqlDbType.
        /// </summary>
        /// <param name="SqlDbType">Type of the db.</param>
        /// <returns></returns>
        public static Type ToSqlDbType(this SqlDbType SqlDbType)
        {
            // convert to DbType
            var dbType = SqlDbType.ToDbType();
            var dummyParameter = new Parameter("dummyname", dbType);
            return dummyParameter.Type.ToType();
        }

        /// <summary>
        /// Convert to DbType from SqlDbType.
        /// </summary>
        /// <param name="SqlDbType">Type of the SQL db.</param>
        /// <returns></returns>
        public static DbType ToDbType(this SqlDbType SqlDbType)
        {
            var dummyParameter = new SqlParameter("dummyname", SqlDbType);
            return dummyParameter.DbType;
        }

        /// <summary>
        /// Convert to SqlDbType from DbType.
        /// </summary>
        /// <param name="dbType">Type of the db.</param>
        /// <returns></returns>
        public static SqlDbType ToSqlDbType(this DbType dbType)
        {
            var dummyParameter = new SqlParameter()
                {
                    ParameterName = "dummyname",
                    DbType = dbType
                };
            return dummyParameter.SqlDbType;
        }

        /// <summary>
        /// Convert to SqlDbType from Type.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        public static SqlDbType ToSqlDbType(this Type type)
        {
            var dummyInstance = Activator.CreateInstance(type);
            var dummyParameter = new SqlParameter("dummyname", dummyInstance);

            // return String as the SqlDbType
            return dummyParameter.SqlDbType;
        }

        /// <summary>
        /// Convert to DbType from Type.
        /// </summary>
        /// <param name="type">The type.</param>
        /// <returns></returns>
        public static DbType ToDbType(this Type type)
        {
            var dummyInstance = Activator.CreateInstance(type);
            var dummyParameter = new SqlParameter("dummyname", dummyInstance);

            // return String as the SqlDbType
            return dummyParameter.DbType;
        }

        #endregion

        /// <summary>
        /// Gets the columns from SQL command.
        /// </summary>
        /// <param name="tSQL">The t SQL.</param>
        /// <param name="connectionStringSettings">The connection string settings.</param>
        /// <returns></returns>
        public static Dictionary<String, TypeCode> GetColumnsFromSqlCommand(String tSQL, ConnectionStringSettings connectionStringSettings)
        {
            var columns = new Dictionary<String, TypeCode>();

            switch (connectionStringSettings.ProviderName)
            {
                case "System.Data.SqlClient":
                    // get connection string
                    using (var sqlCon = new SqlConnection(connectionStringSettings.ConnectionString))
                    {
                        // get SQL adaptor
                        var da = new SqlDataAdapter(tSQL, sqlCon);
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                        try
                        {
                            // create new dataset
                            DataSet ds = new DataSet();
                            da.FillSchema(ds, SchemaType.Source);

                            foreach (DataColumn dc in ds.Tables[0].Columns)
                            {
                                // add column to SQLColumns
                                columns.Add(dc.ColumnName, dc.DataType.ToString().Replace("System.", "").ToEnum<TypeCode>());
                            }
                        }
                        catch (Exception ex)
                        {
                            var test = ex.Message;
                        }
                    }
                    break;
                //case "Oracle.DataAccess.Client":
                //    // get connection string
                //    using (var oraCon = new OracleConnection(connectionStringSettings.ConnectionString))
                //    {
                //        // get SQL adaptor
                //        var da = new OracleDataAdapter(tSQL, oraCon);
                //        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                //        try
                //        {
                //            // create new dataset
                //            DataSet ds = new DataSet();
                //            da.FillSchema(ds, SchemaType.Source);

                //            foreach (DataColumn dc in ds.Tables[0].Columns)
                //            {
                //                // add column to SQLColumns
                //                columns.Add(dc.ColumnName, dc.DataType.ToString().Replace("System.", "").ToEnum<TypeCode>());
                //            }
                //        }
                //        catch (Exception ex)
                //        {
                //            var test = ex.Message;
                //        }
                //    }
                //    break;
                default:
                    break;
            }


            return columns;
        }

        /// <summary>
        /// Gets the columns from sproc.
        /// </summary>
        /// <param name="sproc">The sproc.</param>
        /// <param name="connectionStringSettings">The connection string settings.</param>
        /// <param name="sqlPrameters">The parameters.</param>
        /// <returns></returns>
        public static Dictionary<String, TypeCode> GetColumnsFromSproc(String sproc, ConnectionStringSettings connectionStringSettings, List<SqlParameter> sqlPrameters)
        {
            var columns = new Dictionary<String, TypeCode>();

            // get connection string
            using (var sqlCon = new SqlConnection(connectionStringSettings.ConnectionString))
            {
                // create SQL command for SPROC
                var sqlCmd = new SqlCommand(sproc, sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                // add parameters
                foreach (var p in sqlPrameters)
                {
                    sqlCmd.Parameters.Add(p);
                }

                // get SQL adaptor
                var da = new SqlDataAdapter(sqlCmd);

                // create new dataset
                DataSet ds = new DataSet();
                da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                da.Fill(ds);

                if (ds.Tables.Count > 0)
                {
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        columns.Add(dc.ColumnName, dc.DataType.ToString().Replace("System.", "").ToEnum<TypeCode>());
                    }
                }
            }

            return columns;
        }

        /// <summary>
        /// Gets the sproc parmeters.
        /// </summary>
        /// <param name="sqlProc">The SQL proc.</param>
        /// <param name="connectionStringSettings">The connection string settings.</param>
        /// <returns></returns>
        public static Dictionary<String, SqlDbType> GetSprocParmeters(String sqlProc, ConnectionStringSettings connectionStringSettings)
        {
            var parameters = new Dictionary<String, SqlDbType>();
            using (var sqlCon = new SqlConnection(connectionStringSettings.ConnectionString))
            {
                SqlCommand sqlCmd = new SqlCommand(sqlProc, sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCon.Open();
                SqlCommandBuilder.DeriveParameters(sqlCmd);

                foreach (SqlParameter p in sqlCmd.Parameters.OfType<SqlParameter>().Where(p => p.Direction == ParameterDirection.Input))
                {
                    parameters.Add(p.ParameterName.Replace("@", ""), p.SqlDbType);
                }
            }
            return parameters;
        }

        /// <summary>
        /// Gets the SQL parameter default value.
        /// </summary>
        /// <param name="sprocParameter">The SPROC parameter.</param>
        /// <returns></returns>
        public static String GetSqlParameterDefaultValue(this SqlDbType dataType)
        {
            Object objValue;

            switch (dataType)
            {
                case SqlDbType.Date:
                case SqlDbType.DateTime:
                case SqlDbType.DateTime2:
                case SqlDbType.Time:
                    objValue = SqlDateTime.MinValue;
                    break;
                case SqlDbType.SmallDateTime:
                    objValue = DateTime.Today;
                    break;
                case SqlDbType.SmallInt:
                case SqlDbType.Int:
                case SqlDbType.BigInt:
                    objValue = Activator.CreateInstance(typeof(Int16));
                    break;
                case SqlDbType.TinyInt:
                    objValue = new Byte();
                    break;

                case SqlDbType.Money:
                case SqlDbType.SmallMoney:
                case SqlDbType.Float:
                case SqlDbType.Decimal:
                    objValue = Activator.CreateInstance(typeof(Decimal));
                    break;
                case SqlDbType.Real:
                    objValue = Activator.CreateInstance(typeof(Single));
                    break;
                case SqlDbType.Bit:
                    objValue = Activator.CreateInstance(typeof(Boolean));
                    break;

                case SqlDbType.Char:
                case SqlDbType.VarChar:
                case SqlDbType.NChar:
                case SqlDbType.NText:
                case SqlDbType.NVarChar:
                case SqlDbType.Text:
                case SqlDbType.Xml:
                    objValue = String.Empty;
                    break;

                case SqlDbType.DateTimeOffset:
                    objValue = new DateTimeOffset();
                    break;

                case SqlDbType.Binary:
                    objValue = new Byte[0];
                    break;
                case SqlDbType.Timestamp:
                case SqlDbType.VarBinary:
                case SqlDbType.Image:
                case SqlDbType.Structured:
                case SqlDbType.Udt:
                case SqlDbType.UniqueIdentifier:
                case SqlDbType.Variant:
                default:
                    objValue = null;
                    break;
            }

            return objValue.ToString();
        }

        //private SqlDbType GetSqlDbType(System.Type theType)
        //{
        //    System.Data.SqlClient.SqlParameter p1 = null;
        //    System.ComponentModel.TypeConverter tc = null;
        //    p1 = new System.Data.SqlClient.SqlParameter();
        //    tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.SqlDbType);
        //    if (tc.CanConvertFrom(theType))
        //    {
        //        p1.SqlDbType = tc.ConvertFrom(theType.Name);
        //    }
        //    else
        //    {
        //        //Try brute force
        //        try
        //        {
        //            p1.SqlDbType = tc.ConvertFrom(theType.Name);
        //        }
        //        catch (Exception ex)
        //        {
        //            //Do Nothing
        //        }
        //    }
        //    return p1.SqlDbType;
        //}
    }
}
